clear
set more off


set matsize 800 


scalar dodd = qofd(mdy(07,21,2010))
*sum datequarter
* scalar maxquarter = `r(max)'
scalar crisis_start = qofd(mdy(12,01,2007))
scalar crisis_end   = qofd(mdy(06,01,2009))



* --------------------------------------------------------------------------------
* Table 1: Summary statistics
*-------------------------------------------------------------------------------


insheet using "bhc_and_commercial_hh_min10_branches_for_matlab_nobranchconstraints.csv", clear



gen datequarter = yq(floor(datequarter_num), 1+4*mod(datequarter_num,1))
format %tq datequarter
*keep if assets>3 // drop tiny community banks with assets below 1 billion, otherwise the model cannot fit the lower tail
keep if assets>=1 // drop tiny community banks with assets below 1 billion, otherwise the model cannot fit the lower tail

gen year = year(dofq(datequarter))


xtset rssdid datequarter

gen assets_growth_yoy = 100*(log(assets) - log(l4.assets))
winsor2 assets_growth_yoy, cut(1 99) replace

bysort rssdid: egen assets_growth_yoy_sd = sd(assets_growth_yoy)

global vartokeep_sum netinc  marketing_services_expenses salaries legal_expenses data_processing_expenses ///
cons_and_advisory_expenses engaged_in_ext_audit_dummy print_and_supplies_expenses stock_based_employee_salaries ///
account_and_audit_expenses communications_expenses

global vartokeep_av assets numemployees equity deposits loans tradingassets ///
securitiesavailableforsale tradingliabilities cash


*collapse (sum) $vartokeep_sum (mean) $vartokeep_av, by(year rssdid)

*gen datequarter = qofd(mdy(12,31,year))


*keep if quarter(dofq(datequarter)) == 4
 
gen profit_pre_tax = operinc-operexp
gen profit_pre_tax_ma4 = (profit_pre_tax + l1.profit_pre_tax + l2.profit_pre_tax + l3.profit_pre_tax)/4
gen profit_pre_tax_ma8 = (profit_pre_tax + l1.profit_pre_tax + l2.profit_pre_tax + l3.profit_pre_tax + l4.profit_pre_tax + l5.profit_pre_tax + l6.profit_pre_tax + l7.profit_pre_tax)/8
bysort rssdid: egen profit_pre_tax_tsmean = mean(profit_pre_tax)
bysort rssdid: egen assets_tsmean = mean(assets)

label var assets_growth_yoy "Assets growth rate"
label var assets_growth_yoy_sd "Assets growth rate (sd)"
label var assets "Assets"
label var netinc "Net income"
label var numemployees "\# employees"
label var salaries "Salaries"
label var legal_expenses "Legal"
label var data_processing_expenses "Data processing"
label var cons_and_advisory_expenses "Advisory"
label var print_and_supplies_expenses "Printing and supplies"
label var account_and_audit_expenses "Auditing"
label var communications_expenses "Communications"
label var total_exp "Total admin expenses"
label var profit_pre_tax "Profits"
label var profit_pre_tax_ma4 "Profits (LTM)"

foreach var of varlist  numemployees {
replace `var' =(`var')/(assets*1e3)
winsor2 `var', cut(1 99) replace
}

foreach var of varlist  netinc profit_pre_tax_tsmean profit_pre_tax_ma4 profit_pre_tax_ma8 profit_pre_tax salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses {
replace `var' = 100*(1e3*`var'*4)/(assets*1e9)
winsor2 `var', cut(1 99) replace
}

gen profit_pre_tax_tsmean_per_assets = 100*(1e3*profit_pre_tax_tsmean*4)/(assets_tsmean*1e9)
winsor2 profit_pre_tax_tsmean_per_assets, cut(1 99) replace


global sumvariables  assets assets_growth_yoy assets_growth_yoy_sd profit_pre_tax total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses  numemployees salaries 

sum $sumvariables  if post==0 & assets >= 10.5 & assets <= 11.5
sum $sumvariables  if post==0 & assets >= 10.5 & assets <= 11.5

sum profit_pre_tax if assets >= 10.5 & assets <= 11.5 & post ==0 
sum profit_pre_tax if assets >= 9.5 & assets <= 10.5 & post == 0
sum profit_pre_tax if assets >= 10.5 & assets <= 11.5 
sum profit_pre_tax if assets >= 9.5 & assets <= 10.5

set more off
eststo clear
estpost su  $sumvariables  if datequarter >= qofd(mdy(01,01,2001)) &  datequarter < qofd(mdy(12,31,2019)) ,d
est store A
esttab using   "summary_stat_2001_2019.tex", replace ///
cells("count(fmt(%9.0f)) mean(fmt(%9.3f)) sd(fmt(%9.3f))  p5(fmt(%9.3f)) p25(fmt(%9.3f)) p50(fmt(%9.3f)) p75(fmt(%9.3f)) p95(fmt(%9.3f)) ") ///
label booktabs nonum gaps noobs ///
collabels( "N" "mean" "sd" "p5" "p25" "p50" "p75" "p95") width(\hsize) 


*==================================================================================
* Table 7: DID Table
*==================================================================================

insheet using "bhc_and_commercial_hh_min10_branches_for_matlab_nobranchconstraints.csv", clear


gen datequarter = yq(floor(datequarter_num), 1+4*mod(datequarter_num,1))
format %tq datequarter

gen year = year(dofq(datequarter))
	

xtset rssdid datequarter

gen assets_growth_yoy = 100*(log(assets) - log(l4.assets))
winsor2 assets_growth_yoy, cut(1 99) replace


global vartokeep_sum netinc   salaries legal_expenses data_processing_expenses ///
cons_and_advisory_expenses print_and_supplies_expenses  ///
account_and_audit_expenses communications_expenses

global vartokeep_av assets numemployees equity deposits loans tradingassets ///
securitiesavailableforsale tradingliabilities cash


*collapse (sum) $vartokeep_sum (mean) $vartokeep_av, by(year rssdid)

*gen datequarter = qofd(mdy(12,31,year))


*keep if quarter(dofq(datequarter)) == 4
 
gen profit_pre_tax = operinc-operexp

label var assets_growth_yoy "Assets growth rate"
label var assets "Assets"
label var netinc "Net income"
label var numemployees "\# employees"
label var salaries "Salaries"
label var legal_expenses "Legal"
label var data_processing_expenses "Data processing"
label var cons_and_advisory_expenses "Advisory"
label var print_and_supplies_expenses "Printing and supplies"
label var account_and_audit_expenses "Auditing"
label var communications_expenses "Communications"
label var total_exp "Total admin expenses"
label var profit_pre_tax "Profits"


foreach var of varlist  numemployees {
replace `var' =(`var')/(assets*1e3)
winsor2 `var', cut(1 99) replace
}

foreach var of varlist  netinc profit_pre_tax salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses {
replace `var' = 100*(1e3*`var'*4)/(assets*1e9)
winsor2 `var', cut(1 99) replace
}



global sumvariables  assets assets_growth_yoy total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses  numemployees salaries profit_pre_tax


scalar dodd = qofd(mdy(07,21,2010))
scalar econg = qofd(mdy(05,21,2018))
sum datequarter
scalar maxquarter = `r(max)'
scalar crisis_start = qofd(mdy(12,01,2007))
scalar crisis_end   = qofd(mdy(06,01,2009))


*replace assets = assets_2009
* keep if year >= 2010 & year <= 2017 & assets >= 3 & assets <= 50
* keep if datequarter >= `=dodd' & datequarter < `=econg'
*keep if assets >= 1 & assets <= 25 // already handled later with the treat variable


* Garicano style
capture drop post
gen post = 2 if (datequarter >= `=dodd' & datequarter < `=econg') // post DF
replace post =0 if (datequarter < `=crisis_start')  // pre DF
replace post = 1 if datequarter >= `=crisis_start' & datequarter <= `=crisis_end' // Crisis
* drop if post == .

keep if datequarter < `=econg'
keep if year >= 2003 // no many banks existed there and still during the DF

gen asset_cat6_10  = assets>=3 & assets <10 & datequarter == `=dodd'
bysort rssdid: egen final_asset_cat6_10 = max(asset_cat6_10) // takes the non-na

gen asset_cat10_14 = assets >=10 & assets <40 & datequarter == `=dodd'
bysort rssdid: egen final_asset_cat10_14 = max(asset_cat10_14)

gen Treat = 1 if final_asset_cat10_14 == 1
replace Treat = 0 if final_asset_cat6_10 == 1
drop if Treat ==.




gen Post 		 = datequarter>= `=dodd'
gen Post_X_Treat = Post * Treat

label var Post_X_Treat "Treat * Post"

xtset rssdid datequarter, q


gen ln_branchnumber = ln(brnumber_hh)

global modelnames
eststo clear
foreach VAR in numemployees salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses {
	
	capture drop `VAR'_temp
	gen `VAR'_temp = `VAR'
	replace `VAR'_temp = . if `VAR'_temp <= 0
	global X `VAR'_temp

	/*
	if "`VAR'" != "numemployees" {
		replace `VAR'_temp = `VAR'_temp * 100
	}
	*/
	eststo: xtreg `VAR'_temp i.datequarter ln_branchnumber  Post_X_Treat, vce(cluster rssdid) fe
	global modelnames $modelnames "`:var label `VAR'' "
	estadd local Controls "Yes"
	estadd local BankFE "Yes"
	estadd local QuarterFE "Yes"

}


esttab  using "Diff_in_diff_cost_of_regulation_scaled_by_assets_newdata.tex", ///
replace se star(* 0.10 ** 0.05 *** 0.01  )  b(%8.3f) ///
mtitles("\# employees" "Salaries" "\shortstack{Total admin\\expenses}" "Legal" "\shortstack{Data\\processing}" "Advisory" "Printing" "Auditing" "\shortstack{Com-\\munications}") ///
ar2  br  label  nonotes  gaps ///
keep(Post_X_Treat ) /// 
s(Controls BankFE QuarterFE N r2_a, label("Controls" "Bank FE" "Quarter FE" "Observations" "Adj. R-squared")fmt(0 0 0 %9.0fc 3)) ///
width(\hsize) 


*---------------------------------------------------------------------------------
* Table 8 and Figure 9: REGRESSION DISCONTINUITY 
*---------------------------------------------------------------------------------

insheet using "bhc_and_commercial_hh_min10_branches_for_matlab_nobranchconstraints.csv", clear

gen datequarter = yq(floor(datequarter_num), 1+4*mod(datequarter_num,1))
format %tq datequarter

gen year = year(dofq(datequarter))
	

xtset rssdid datequarter

gen assets_growth_yoy = 100*(log(assets) - log(l4.assets))
winsor2 assets_growth_yoy, cut(1 99) replace


global vartokeep_sum netinc   salaries legal_expenses data_processing_expenses ///
cons_and_advisory_expenses print_and_supplies_expenses  ///
account_and_audit_expenses communications_expenses

global vartokeep_av assets numemployees equity deposits loans tradingassets ///
securitiesavailableforsale tradingliabilities cash


*collapse (sum) $vartokeep_sum (mean) $vartokeep_av, by(year rssdid)

*gen datequarter = qofd(mdy(12,31,year))


*keep if quarter(dofq(datequarter)) == 4
 
gen profit_pre_tax = operinc-operexp

label var assets_growth_yoy "Assets growth rate"
label var assets "Assets"
label var netinc "Net income"
label var numemployees "\# employees"
label var salaries "Salaries"
label var legal_expenses "Legal"
label var data_processing_expenses "Data processing"
label var cons_and_advisory_expenses "Advisory"
label var print_and_supplies_expenses "Printing and supplies"
label var account_and_audit_expenses "Auditing"
label var communications_expenses "Communications"
label var total_exp "Total admin expenses"
label var profit_pre_tax "Profits"


foreach var of varlist  numemployees {
replace `var' =(`var')/(assets*1e3)
winsor2 `var', cut(1 99) replace
}

foreach var of varlist  netinc profit_pre_tax salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses {
replace `var' = 100*(1e3*`var'*4)/(assets*1e9)
winsor2 `var', cut(1 99) replace
}



global sumvariables  assets assets_growth_yoy total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses  numemployees salaries profit_pre_tax



scalar dodd = qofd(mdy(07,21,2010))
scalar econg = qofd(mdy(05,21,2018))
sum datequarter
scalar maxquarter = `r(max)'
scalar crisis_start = qofd(mdy(12,01,2007))
scalar crisis_end   = qofd(mdy(06,01,2009))


*replace assets = assets_2009
* keep if year >= 2010 & year <= 2017 & assets >= 3 & assets <= 50
keep if datequarter >= `=dodd' & datequarter < `=econg'
keep if assets >= 3 & assets <= 40

sum assets
scalar h = `r(sd)' * 1.06 * `r(N)'^(-1/5)



* Local Linear 
gen Treat = cond(assets >= 10,1,0)

gen assets_center = assets - 10
gen assets_center_X_Treat = assets_center * Treat


label var Treat "Treatment Effect"
label var numemployees "# employees"
label var salaries "Salaries"
label var legal_expenses "Legal"
label var data_processing_expenses "Data processing"
label var cons_and_advisory_expenses "Advisory"
label var print_and_supplies_expenses "Printing and supplies"
label var account_and_audit_expenses "Auditing"
label var communications_expenses "Communications"
label var total_exp "Total admin expenses"
label var netinc "Net income (negative)"

gen assets_center2 = assets_center^2
gen assets_center2_X_Treat = assets_center2* Treat

*=============================
**** Quadratic Plots
*=============================

local VAR salaries
disp `=h'
/*
eststo clear
sum year
forvalues var = `r(min)' (1) `r(max)' {
	sum assets if year == `var'
	scalar h = `r(sd)' * 1.06 * `r(N)'^(-1/5)
	disp `=h'
	eststo: regress netinc_annual_m assets_center_m assets_center2 Treat assets_center_X_Treat assets_center2_X_Treat ///
	if year == `var' & assets >= 10 - `=h' & assets <= 10 + `=h', vce(cluster rssdid)
	estadd scalar Bandwidth = `=h'

}
*/

global GRAPH2 
foreach VAR in numemployees salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses /*total_exp*/{

	capture drop `VAR'_max `VAR'_min
	egen `VAR'_max = max(`VAR') if assets >= 10 - `=h' & assets <= 10 + `=h'
	egen `VAR'_min = min(`VAR') if assets >= 10 - `=h' & assets <= 10 + `=h'

	* Quadratic
	local cond  `VAR' <. & `VAR' > 0 //& `VAR' > `VAR'_min & `VAR' < `VAR'_max  // & `VAR' != 0 

	sort assets
	*reg `VAR' assets_center assets_center assets_center_X_Treat assets_center2 assets_center2_X_Treat Treat if assets >= 10 - `=h' & assets <= 10 + `=h'

	reg `VAR' assets_center assets_center assets_center_X_Treat Treat if assets >= 10 - `=h' & assets <= 10 + `=h' & `cond'
	capture drop `VAR'_hat
	predict `VAR'_hat
	scatter `VAR' assets if assets >= 10 - `=h' & assets <= 10 + `=h' & `cond', ylabel(, angle(h) nogrid) msize(tiny) xline(10, lcolor(blue)) ///
	xtitle("") ytitle("") title("`:var label `VAR''",size(medsmall )) ///
	 || line `VAR'_hat assets if Treat ==1 &  assets >= 10 - `=h' & assets <= 10 + `=h' & `cond', color(red) || ///
	line `VAR'_hat assets if Treat ==0 & assets >= 10 - `=h' & assets <= 10 + `=h' & `cond', saving("`VAR'", replace) color(red) legend(off) graphregion(color(white))
	global GRAPH2 ${GRAPH2} "`VAR'.gph "
}


graph combine ${GRAPH2}, graphregion(color(white)) rows(3) cols(3) xcommon

capture:  graph export "RD_plots_all_periods_scaled_by_assets_newdata.pdf", replace


*=============================
**** Quadratic Regressions
*=============================

eststo clear



foreach VAR in numemployees salaries total_exp  legal_expenses data_processing_expenses cons_and_advisory_expenses print_and_supplies_expenses ///
account_and_audit_expenses communications_expenses /*total_exp*/{

	capture drop `VAR'_max `VAR'_min
	egen `VAR'_max = max(`VAR') if assets >= 10 - `=h' & assets <= 10 + `=h'
	egen `VAR'_min = min(`VAR') if assets >= 10 - `=h' & assets <= 10 + `=h'

	* Quadratic
	local cond  `VAR' <. & `VAR' > 0 //& `VAR' > `VAR'_min & `VAR' < `VAR'_max    //& `VAR' != 0

	capture drop `VAR'_temp
	gen `VAR'_temp = `VAR'
	replace `VAR'_temp = `VAR'_temp 

	*eststo: regress `VAR'_temp assets_center assets_center2 Treat assets_center_X_Treat assets_center2_X_Treat ///

	eststo: regress `VAR'_temp assets_center  Treat assets_center_X_Treat  ///
	if assets >= 10 - `=h' & assets <= 10 + `=h' & `cond', vce(cluster rssdid)
	estadd scalar Bandwidth = `=h'

	
}




esttab  using "RD_data_scaled_by_assets_newdata.tex", ///
replace se star(* 0.10 ** 0.05 *** 0.01  )   b(%8.3f) ///
mtitles("\# employees" "Salaries" "\shortstack{Total admin\\expenses}" "Legal" "\shortstack{Data\\processing}" "Advisory" "Printing" "Auditing" "\shortstack{Com-\\munications}") ///
ar2  br  label  nonotes  gaps nonumbers nocons  ///
stats(N r2_a Bandwidth, label("Observations" "Adj. R-squared" "Bandwidth") fmt(%9.0fc 3 2)) ///
width(\hsize) keep(Treat) 



*---------------------------------------------------------------------------------
* Fingure OA.1. Histogram of banks from 4 to 16 billions
*---------------------------------------------------------------------------------

insheet using "bhc_and_commercial_hh_min10_branches_for_matlab_nobranchconstraints.csv", clear

gen datequarter = yq(floor(datequarter_num), 1+4*mod(datequarter_num,1))
format %tq datequarter

/*
capture drop post
gen post = 2 if (datequarter >= qofd(mdy(08,01,2010)) & datequarter <= qofd(mdy(05,01,2018))) // post DF
replace post =0 if (datequarter < `=crisis_start' & datequarter >= qofd(mdy(08,01,2000))) // pre DF
replace post = 1 if datequarter >= `=crisis_start' & datequarter <= `=crisis_end' // Crisis
*/

global assetvar assets 
label var assets "Assets (billion dollars)" 
capture drop Treatment_q
gen Treatment_q = .
forval i = 1/30 {
	quietly replace Treatment_q = `i' if ${assetvar}>= (`i'-1) & ${assetvar} < (`i'-1) + 1
	}

	
drop if post == . | Treatment_q ==.


local title "Post Dodd-Frank (2010Q3 - 2018Q2)"
hist assets if post == 2 & assets >=4 & assets <=16, ///
bin(40) title(`title') graphregion(color(white)) percent saving(hist_postDF.gph, replace) xline(10)

local title "Pre Dodd-Frank (2001Q1 - 2007Q3)"
hist assets if post == 0 & assets >=4 & assets <=16, ///
bin(40) title(`title') graphregion(color(white)) percent saving(hist_preDF.gph, replace) xline(10)

graph combine  hist_preDF.gph hist_postDF.gph , graphregion(color(white)) iscale(*0.75) rows(2)

graph export "histogram_assets_pre_post_DF.pdf", replace


********************************************************************************************
*  Figure 7 b) Bank valuation
********************************************************************************************


use "bank_repurchase_div_yield.dta", clear
g MB = mcap_c/BE0
g MB_assets = (mcap_c+10*BE0)/(BE0+10*BE0)
winsor MB, g(MB_w) p(.01)
winsor MB_assets, g(MB_assets_w) p(.01)

g year =fyear
collapse (mean) MB_w MB_assets_w [iw=BE0], by(year)


 g mean_MB = .
 sum MB_assets_w if year>=2000 & year<2010
 replace mean_MB = `r(mean)' if year>=2000 & year<2010
  sum MB_assets_w if year>=2010 & year<=2020
 replace mean_MB = `r(mean)' if year>=2010 & year<=2020

  
 tsset year
twoway (tsline MB_assets_w , lpattern(solid ) lcolor(navy )) (tsline mean_MB, lpattern(dash)) ///
if year>=2000, xlab(2000(5)2020) ylab(, nogrid)   xtitle("") ytitle("Bank valuation") title("") ///
graphregion(color(white))    legend( off ) tline(2010)
 graph export "ts_MB_assets_2000_2020.pdf", replace 

 
 ********************************************************************************
* Entry and Exit Rate
********************************************************************************
use entry_exits.dta,clear


order rssdid year assets depsum depdom depsumbr asset

* Exit rate over time:
collapse (sum) entry exit (count) rssdid, by(year)
tset year, y
gen entry_rate = 100*entry/l1.rssdid
gen exit_rate  = 100*exit/rssdid

keep if year >= 1995 & year <= 2019
tsset year, y

replace exit_rate = . if year == 2019
capture drop post
gen post     =  "Post DF"  if year >= 2011 & year < 2018
replace post ="Pre DF" if  year >= 2001 & year < 2008

tab post, summarize(entry_rate)
tab post, summarize(exit_rate)

label var entry_rate "Entry rate"
label var exit_rate "Exit rate"

capture gen exit_rate_neg = -l1.exit_rate
label var exit_rate_neg "Exit rate (total)"

graph twoway (bar entry_rate year if year >= 2000 & year <=  2019) ///
(bar exit_rate_neg year if year >= 2000 & year <=  2019), xline(2010) ylab(, nogrid angle(h)) graphregion(color(white)) ///
xtitle("") ytitle("%")

graph export "entry_exit_rate_with_line.pdf", replace




